# -*- coding: utf-8 -*-

import re

import psycopg2
import pymysql



class NamingConverter:
    #驼峰转下划线
    @staticmethod
    def camel_to_snake(camel_case):
        snake_case = re.sub(r'(?<!^)(?=[A-Z])', '_', camel_case).lower()
        return snake_case

    # 下划线转驼峰
    @staticmethod
    def snake_to_camel(snake_case):
        components = snake_case.split('_')
        camel_case = components[0] + ''.join(x.title() for x in components[1:])
        return camel_case

class MySQLUtils:
    def __init__(self, host='43.138.89.98', port=3306, user='root', password='178121', database='db2023'):
        self.converter = NamingConverter()
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.connection = pymysql.connect(host=self.host, user=self.user,port=self.port ,password=self.password,db=self.database,charset='utf8')

    def connect(self):
        return self.connection

    def disconnect(self):
        if self.connection is not None:
            self.connection.close()
            print("Disconnected from MySQL database")

    def execute_query(self, query):
        result = None
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query)
                result = cursor.fetchall()
        except pymysql.Error as e:
            print(f"Error executing query: {e}")
        return result

    def execute_query2(self, query):
        result = None
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query)
                columns = [column[0] for column in cursor.description]
                result = [dict(zip(columns, row)) for row in cursor.fetchall()]
        except pymysql.Error as e:
            print(f"Error executing query: {e}")
        return result

    def execute_update(self, query):
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query)
            self.connection.commit()
            print("Query executed successfully")
        except pymysql.Error as e:
            print(f"Error executing update: {e}")

    def create(self, table, data,camel_to_snake=True,sign_Flag=True,exeute=True):
        keys = list(data.keys())
        if camel_to_snake:
            keys = list(map(self.converter.camel_to_snake, keys))
        if sign_Flag:
            keys=['`'+item+'`'  for item in keys]
        columns = ', '.join(keys)
        values = ', '.join([f"'{self.toStr(value)}'" for value in data.values()])
        query = f"INSERT INTO {table} ({columns}) VALUES ({values}) ;"
        query=query.replace("'NONE'","null",-1)
        if exeute:
            self.execute_update(query)
        return query

    def insert(self, table, data):
        keys = list(data.keys())
        columns = ', '.join(keys)
        values = ', '.join([f"'{self.toStr(value)}'" for value in data.values()])
        sql = f"INSERT INTO {table} ({columns}) VALUES ({values});"
        self.execute_update(sql)

    def insertBatch(self, table, keys,data):
        sql=self.generate_mysql_insert_statement(keys=keys,data=data,table=table)
        print("批量插入语句为"+sql)
        self.execute_update(sql)


    def create_with_check(self, table, data):
        columns = self.retrieve_columns(table)
        keys = list(data.keys())
        keys = list(map(self.converter.camel_to_snake, keys))
        keys=[i for i in keys if i in columns]
        values=[self.toStr(data[key]) for key in data.keys() if self.converter.camel_to_snake(key) in keys]

        keysStr = ', '.join(keys)
        valuesStr = ', '.join([f"'{self.toStr(value)}'" for value in values])

        query = f"INSERT INTO {table} ({keysStr}) VALUES ({valuesStr})"
        print(query)
        self.execute_update(query)

    def update(self, table, data, condition):
        updates = ', '.join([f"{key}='{value}'" for key, value in data.items()])
        query = f"UPDATE {table} SET {updates} WHERE {condition}"
        print(f'更新语句:{query}')
        self.execute_update(query)


    def delete(self, table, condition):
        query = f"DELETE FROM {table} WHERE {condition}"
        self.execute_update(query)

    def retrieve(self, table, condition=''):
        query = f"SELECT * FROM {table}"
        if condition:
            query += f" WHERE {condition}"
        return self.execute_query(query)

    def retrieve_json(self, table, condition=''):
        query = f"SELECT * FROM {table}"
        if condition:
            query += f" WHERE {condition}"
        return self.execute_query2(query)

    def retrieve_count(self, table, condition=''):
        query = f"SELECT count(1) FROM {table}"
        if condition:
            query += f" WHERE {condition}"
        print(f'待执行sql为{query}')
        return self.execute_query(query)[0][0]

    def retrieve_columns(self, table):
        query = f"SHOW COLUMNS FROM {table}"
        execute_query = self.execute_query(query)
        result = [column[0] for column in execute_query]
        return result

    def retrieve_sql(self, sql):
        execute_query = self.execute_query(sql)
        return execute_query

    def update_sql(self, sql):
        self.execute_update(sql)

    def toStr(self, data):
        if isinstance(data, list):
            return '@@'.join(data)
        if data==None:
            return "NONE"
        return data

    def generate_mysql_insert_statement(self, keys, data, table):
        # 检查输入的键集合和字典集合是否为空
        if not keys or not data:
            return None

        # 构建插入语句的列部分
        columns = ", ".join(keys)

        # 构建插入语句的值部分
        values = []
        for entry in data:
            # 检查每个字典中是否包含与键集合对应的键，若不包含则跳过
            if not all(key in entry for key in keys):
                continue

            # 获取当前字典中与键集合对应的值，并处理为 MySQL 插入语句中的格式
            value_str = ", ".join(["'{}'".format(entry[key]) for key in keys])
            values.append("({})".format(value_str))

        # 构建完整的插入语句
        insert_statement = "INSERT INTO {} ({}) VALUES {};".format(table, columns, ", ".join(values))
        return insert_statement



class PostgresqlUtiles(object):
    # 可以初始化好，就不需要传值，database建议不传，因为你要查那个表在传的话会更灵活
    def __init__(self, database=None, user="pgroot",
                 password="wuukT9DW2fx54!C^",
                 host="172.16.10.53", port=5432):
        self._cursor = None  # 游标
        self._conn = None  # 链接数据库
        self.database = database
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.result = None

    # 链接数据库
    def get_connection(self):
        self._conn = psycopg2.connect(database=self.database,
                                      user=self.user, password=self.password,
                                      host=self.host, port=self.port
                                      )

    # 关闭数据库连接
    def close_connection(self):
        # 事务提交
        self._conn.commit()
        # 关闭数据库连接
        self._cursor.close()
        self._conn.close()

    # 执行一条sql  带参数
    def execute_sql_params(self, sql, params=None):
        self._cursor = self._conn.cursor()
        try:
            print(f"当前执行sql：{sql}，参数：{params}")
            # 执行语句
            self._cursor.execute(sql, params)
        except psycopg2.Error as e:
            print(f"执行sql：{sql}，出错，错误原因：{e}")

    # 通用执行方法
    def execute_method(self, sql, params=None, method_name=None):
        # 获取连接
        self.get_connection()
        # 执行sql
        self.execute_sql_params(sql, params)
        if method_name is not None:
            # 查询单条
            if "find_one" == method_name:
                self._result = self._cursor.fetchone()
            # 查询全部
            elif "find_all" == method_name:
                self._result = self._cursor.fetchall()
        # 关闭数据库连接
        self.close_connection()

    # 查询单条
    def find_one(self, sql, params=None):
        self.execute_method(sql, params=params, method_name="find_one")
        return self._result

    # 查询所有
    def find_all(self, sql, params=None):
        self.execute_method(sql, params=params, method_name="find_all")
        return self._result

    # 插入
    def insert(self, sql, params=None):
        self.execute_method(sql, params=params)

    # 更新
    def update(self, sql, params=None):
        self.execute_method(sql, params=params)

    # 删除
    def delete(self, sql, params=None):
        self.execute_method(sql, params=params)



if __name__ == "__main__":
    # 示例用法
    # db = MySQLUtils('43.138.89.98', 3306, 'root', '178121', 'db2023')
    #
    # condition = "market_labels like'{}'".format('%分红型%')
    # count = db.retrieve_count(table='crawer_data',condition=condition)
    # print(count)

    # # 创建记录
    # data=[{'productId': 'NPRO20210817000001', 'companyId': 'COM20190304000001', 'companyName': '昆仑健康保险', 'productType': 'M',
    #   'productName': '护身福保险产品计划（乐享传家终身护理保险为必投险种）', 'shortProductName': '护身福保险产品计划（乐享传家终身护理保险为必投险种）',
    #   'productChannel': 'network', 'saleLabels': [], 'unSaleLabels': ['停售'], 'marketLabels': ['护理保险', '保终身'],
    #   'conditionLabels': ['投保年龄：出生满28天（含）-65周岁（含）', '保障期间：终身', '职业类别：1-6类', '生效日期：T+1'],
    #   'homeHeaderImgPath': 'https://kbc-prod.dtinsure.com/gateway/oss4/insMarket/B00021/T0001/ruleHeaderImg/2021/05/27/LR000008/1397731801500356610/18.png?Expires=2252800205&OSSAccessKeyId=LTAIb4cgSBDiBaC8&Signature=5AjBgtDVB2eoL%2FsbNI6kGAgkYAk%3D',
    #   'productDetailLink': 'https://kbc-prod.dtinsure.com/np/productDetail?productId=NPRO20210817000001',
    #   'productPCDetailLink': 'https://kbc-prod.dtinsure.com/planbook-pc/planbook/productDetail?productId=NPRO20210817000001',
    #   'productDataLink': 'https://kbc-prod.dtinsure.com/np/productData?productId=NPRO20210817000001',
    #   'payPeriodLabel': '期缴', 'minPrem': '10000', 'commissionShowType': 1, 'shareType': 'network', 'accountType': 3,
    #   'redirectType': 1, 'redirectUrl': 'https://kbc-prod.dtinsure.com/np/productDetail?productId=NPRO20210817000001',
    #   'onlineWhiteList': False, 'productStatus': 1, 'insureCode': 'HL-CQHL', 'signing': True, 'saleChannel': 'internet',
    #   'sale': False}]
    #
    # # data = {'name': 'John', 'age': 25, 'email': 'john@example.com'}
    # time_util = TimeUtil()
    # data[0]['id']=time_util.generateUuid()
    # db.create('crawer_data', data[0])
    # #
    # # # 更新记录
    # data = {'age': 26, 'email': 'john_updated@example.com'}
    # condition = "name='John'"
    # db.update('student', data, condition)

    # 删除记录
    # condition = "name='John'"
    # db.delete('student', condition)

    # 检索记录
    # result = db.retrieve_json('student')
    # print(result)
    sql="""
    INSERT INTO audit_dict (`id`, `pid`, `type`, `code`, `content`, `describe`, `ext1`, `ext2`, `canEdit`, `status`, `createTime`, `createUser`, `updateTime`, `updateUser`, `seq`) VALUES ('0', 'NONE', 'z_operate_id', '9001_0001', '测试', '', '', '', '1', '1', '2024-05-30 22:21:13', 'None', '2024-05-30 22:21:13', 'None', 'NONE')
    """
    print(sql.replace("'NONE'","null",-1))

